CUBRID
Introduction to Manual
Manual Contents
Glossary
Manual Conventions
Version Name and Version String Conventions
Introduction to CUBRID
System Architecture
System Architecture
Database Volume Structure
Permanent Volume
Temporary Volume
Backup Volume
Database Server
Broker
Interface Module
CUBRID Characteristics
Installing and Upgrading
Installing and Running CUBRID
Supported Platforms and System Requirements
Compatibility
Interoperability
Installation and Running CUBRID on Linux
Installing and Running CUBRID on Windows
Configuring Environment Variables
CUBRID Environment Variables
OS Environment and Java Environment Variables
Language Setting
Port Setting
Default Ports for CUBRID
Ports for CUBRID HA
Ports for CUBRID SHARD
Ports for CUBRID Web Manager and CUBRID Manager Server
Upgrade
Cautions During Upgrade
Upgrading From CUBRID 2008 R4.1, R4.3 To R4.4
DB migration
Parameter configuration
Upgrading From CUBRID 2008 R4.0 or Earlier Versions To R4.4
DB migration
Parameter configuration
Database Migration Procedures under HA Environment
HA migration from CUBRID 2008 R2.2 or higher to R4.4
HA Migration from CUBRID 2008 R2.0/R2.1 to R4.4
Getting Started
Starting the CUBRID Service
Shell Command
CUBRIDService or CUBRID Service Tray
Creating Databases
Starting Database
Query Tools
CSQL Interpreter
Management Tools
Running SQL with CUBRID Web Manager
Running SQL with CUBRID Manager Client
Running SQL with CUBRID Query Browser
Migrating schema/data with CUBRID Migration Toolkit
Drivers
CSQL Interpreter
Introduction to the CSQL Interpreter
Executing CSQL
CSQL Execution Mode
Using CSQL (Syntax)
CSQL Options
Session Commands
CUBRID SQL Guide
Identifier
Guidelines for Creating Identifiers
Legal Identifiers
Illegal Identifiers
The maximum length of an identifier name
Reserved Words
Comment
Data Types
Numeric Types
INT/INTEGER
SHORT/SMALLINT
BIGINT
NUMERIC/DECIMAL
FLOAT/REAL
DOUBLE/DOUBLE PRECISION
Date/Time Types
DATE
TIME
TIMESTAMP
DATETIME
CASTing a String to Date/Time Type
Bit Strings
BIT(n)
BIT VARYING(n)
Character Strings
CHAR(n)
VARCHAR(n)/CHAR VARYING(n)
STRING
NCHAR(n)
NCHAR VARYING(n)
Escape Special Characters
BLOB/CLOB Data Types
BLOB
CLOB
To Create and alter LOB
To store and update LOB
To access LOB
Functions and Operators for LOB
To create and manage LOB storage
Transaction and Recovery
Collection Types
SET
MULTISET
LIST/SEQUENCE
Implicit Type Conversion
Conversion Rules
Table
CREATE TABLE
Table Definition
Column Definition
Constraint Definition
Column Option
Table Option
CREATE TABLE LIKE
CREATE TABLE AS SELECT
ALTER TABLE
ADD COLUMN Clause
ADD CONSTRAINT Clause
ADD INDEX Clause
ALTER COLUMN ... SET DEFAULT Clause
AUTO_INCREMENT Clause
CHANGE/MODIFY Clauses
RENAME COLUMN Clause
DROP COLUMN Clause
DROP CONSTRAINT Clause
DROP INDEX Clause
DROP PRIMARY KEY Clause
DROP FOREIGN KEY Clause
DROP TABLE
RENAME TABLE
Index
CREATE INDEX
ALTER INDEX
DROP INDEX
View
CREATE VIEW
Condition for Creating Updatable VIEW
ALTER VIEW
ADD QUERY Clause
AS SELECT Clause
CHANGE QUERY Clause
DROP QUERY Clause
DROP VIEW
RENAME VIEW
Serial
CREATE SERIAL
ALTER SERIAL
DROP SERIAL
Accessing Serial
Pseudocolumns
Functions
Database User Authorization
Database User
Managing User
Granting Authorization
Revoking Authorization
User Authorization Management METHOD
Operators and Functions
Logical Operators
Comparison Operators
Arithmetic Operators
Arithmetic Operators
Arithmetic Operations and Type Casting of Numeric Data Types
Arithmetic Operations and Type Casting of DATE/TIME Data Types
Set Arithmetic Operators
SET, MULTISET, LIST
Statement Set Operators
UNION, DIFFERENCE, INTERSECTION
Containment Operators
SETEQ
SETNEQ
SUPERSET
SUPERSETEQ
SUBSET
SUBSETEQ
BIT Functions and Operators
Bitwise Operator
BIT_AND
BIT_OR
BIT_XOR
BIT_COUNT
String Functions and Operators
Concatenation Operator
ASCII
BIN
BIT_LENGTH
CHAR_LENGTH, CHARACTER_LENGTH, LENGTHB, LENGTH
CHR
CONCAT
CONCAT_WS
ELT
FIELD
FIND_IN_SET
INSERT
INSTR
LCASE, LOWER
LEFT
LOCATE
LPAD
LTRIM
MID
OCTET_LENGTH
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRANSLATE
TRIM
UCASE, UPPER
Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
CEIL
CONV
COS
COT
DEGREES
DRANDOM, DRAND
EXP
FLOOR
HEX
LN
LOG2
LOG10
MOD
PI
POW, POWER
RADIANS
RANDOM, RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNC, TRUNCATE
Date/Time Functions and Operators
ADDDATE, DATE_ADD
ADDTIME
ADD_MONTHS
CURDATE, CURRENT_DATE, SYS_DATE, SYSDATE
CURRENT_DATETIME, NOW, SYS_DATETIME, SYSDATETIME
CURTIME, CURRENT_TIME, SYS_TIME, SYSTIME
CURRENT_TIMESTAMP, SYS_TIMESTAMP, SYSTIMESTAMP, LOCALTIME, LOCALTIMESTAMP
DATE
DATEDIFF
DATE_SUB, SUBDATE
DAY, DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
HOUR
LAST_DAY
MAKEDATE
MAKETIME
MINUTE
MONTH
MONTHS_BETWEEN
QUARTER
SEC_TO_TIME
SECOND
TIME
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
WEEK
WEEKDAY
YEAR
LOB Functions
BIT_TO_BLOB
BLOB_FROM_FILE
BLOB_LENGTH
BLOB_TO_BIT
CHAR_TO_BLOB
CHAR_TO_CLOB
CLOB_FROM_FILE
CLOB_LENGTH
CLOB_TO_CHAR
Data Type Casting Functions and Operators
CAST
DATE_FORMAT
FORMAT
STR_TO_DATE
TIME_FORMAT
TO_CHAR(date_time)
TO_CHAR(number)
TO_DATE
TO_DATETIME
TO_NUMBER
TO_TIME
TO_TIMESTAMP
Aggregate Functions
Overview
AVG
COUNT
GROUP_CONCAT
MAX
MIN
STDDEV, STDDEV_POP
STDDEV_SAMP
SUM
VARIANCE, VAR_POP
VAR_SAMP
Click Counter Functions
INCR, DECR
ROWNUM Functions
ROWNUM, INST_NUM
ORDERBY_NUM
GROUPBY_NUM
Information Functions
CURRENT_USER, USER
DATABASE, SCHEMA
DEFAULT
INDEX_CARDINALITY
INET_ATON
INET_NTOA
LAST_INSERT_ID
LIST_DBS
ROW_COUNT
USER, SYSTEM_USER
VERSION
Encryption Function
MD5
Comparison Expression
Simple Comparison Expression
ANY/SOME/ALL quantifiers
BETWEEN
EXISTS
IN
IS NULL
LIKE
REGEXP, RLIKE
CASE
Comparison Functions
COALESCE
DECODE
GREATEST
IF
IFNULL, NVL
ISNULL
LEAST
NULLIF
NVL2
Data Manipulation
SELECT
FROM Clause
WHERE Clause
GROUP BY ... HAVING Clause
ORDER BY Clause
LIMIT Clause
Join Query
Subquery
Hierarchical Query
START WITH Clause
CONNECT BY Clause
Hierarchical Query Execution
Ordering Data with the Hierarchical Query
Pseudo Columns for Hierarchical Query
Operators for Hierarchical Query
Functions for Hierarchical Query
Examples of Hierarchical Query
Performance of Hierarchical Query
INSERT
INSERT ... SELECT Statement
ON DUPLICATE KEY UPDATE Clause
UPDATE
REPLACE
DELETE
TRUNCATE
PREPARED STATEMENT
PREPARE Statement
EXECUTE Statement
DEALLOCATE PREPARE/DROP PREPARE Statements
SET
DO
SHOW
DESC, DESCRIBE
EXPLAIN
SHOW TABLES
SHOW COLUMNS
SHOW INDEX
SHOW GRANTS
SHOW CREATE VIEW
SHOW EXEC STATISTICS
Query Optimization
Updating Statistics
Checking Statistics Information
Viewing Query Plan
Query Profiling
Using SQL Hint
Index Hint
USING INDEX
USE, FORCE, IGNORE INDEX
Examples of index hint
Optimization using indexes
Covering Index
Optimizing ORDER BY Clause
Index Scan in Descending Order
Optimizing GROUP BY Clause
Partitioning
Partitioning key
Range Partitioning
Hash Partitioning
List Partitioning
Partition Pruning
Partitioning Management
Modifying a Partitioned Table into a Regular Table
Partition Reorganization
Adding Partitions
Dropping Partitions
Hash Partitioning Reorganization
Notes on Partitioning
Statistics on Partitioning Tables
Restrictions on Partitioned Tables
Transaction and Lock
Database Transaction
Transaction Commit
Transaction Rollback
Savepoint and Partial Rollback
Cursor Holdability
Database Concurrency
Lock Protocol
Granularity Locking
Lock Mode Types And Compatibility
Transaction Deadlock
Transaction Lock Timeout
Transaction Isolation Level
SET TRANSACTION ISOLATION LEVEL
GET TRANSACTION ISOLATION LEVEL
SERIALIZABLE
REPEATABLE READ CLASS with REPEATABLE READ INSTANCES
REPEATABLE READ CLASS with READ COMMITTED INSTANCES
REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES
READ COMMITTED CLASS with READ COMMITTED INSTANCES
READ COMMITTED CLASS with READ UNCOMMITTED INSTANCES
UPDATE INCONSISTENCY
Combination of Unsupported Isolation Level
How to Handle Dirty Record
Transaction Termination and Restoration
Restarting Database
Trigger
CREATE TRIGGER
Guidelines for Trigger Definition
Trigger Definition
Event Time
Trigger Type
Trigger Event Type
Trigger Event Target
Combination of Event Type and Target
Trigger Condition
Correlation Name
Trigger Action
ALTER TRIGGER
DROP TRIGGER
RENAME TRIGGER
Deferred Condition and Action
Executing Deferred Condition and Action
Dropping Deferred Condition and Action
Granting Trigger Authorization
Trigger on REPLACE and INSERT ... ON DUPLICATE KEY UPDATE
Trigger Debugging
Viewing Trigger Execution Log
Limiting Nested Trigger
Trigger Example
Java Stored Function/Procedure
Environment Configuration for Java Stored Function/Procedure
Windows Environment
Linux/UNIX Environment
How to Write Java Stored Function/Procedure
Check the cubrid.conf file
Write and compile the Java source code
Load the compiled Java class into CUBRID
Publish the loaded Java class
Call the Java stored function/procedure
Using Server-side Internal JDBC Driver
Connecting to Other Database
loadjava Utility
Loaded Java Class Publish
Call Specifications
Data Type Mapping
Checking the Published Java Stored Function/Procedure Information
Deleting Java Stored Functions/Procedures
Java Stored Function/Procedure Call
Using CALL Statement
Calling from SQL Statement
Calling from Java Application
Caution
Returning Value of Java Stored Function/Procedure and Precision Type on IN/OUT
Returning java.sql.ResultSet in Java Stored Procedure
IN/OUT of Set Type in Java Stored Function/Procedure
Using OID in Java Stored Function/Procedure
Method
Method Type
CALL Statement
Class Inheritance
Class Attribute and Method
Order Rule for Inheritance
INHERIT Clause
ADD SUPERCLASS Clause
DROP SUPERCLASS Clause
Class Conflict Resolution
Resolution Specifier
Superclass Conflict
Sub class Conflict
Schema Invariant
Rule for Schema Changes
System Catalog
System Catalog Classes
_db_class
_db_attribute
_db_domain
_db_method
_db_meth_sig
_db_meth_arg
_db_meth_file
_db_query_spec
_db_index
_db_index_key
_db_auth
_db_data_type
_db_partition
_db_stored_procedure
_db_stored_procedure_args
db_user
db_authorization
db_trigger
db_ha_apply_info
System Catalog Virtual Class
DB_CLASS
DB_DIRECT_SUPER_CLASS
DB_VCLASS
DB_ATTRIBUTE
DB_ATTR_SETDOMAIN_ELM
DB_METHOD
DB_METH_ARG
DB_METH_ARG_SETDOMAIN_ELM
DB_METH_FILE
DB_INDEX
DB_INDEX_KEY
DB_AUTH
DB_TRIG
DB_PARTITION
DB_STORED_PROCEDURE
DB_STORED_PROCEDURE_ARGS
Catalog Class/Virtual Class Authorization
Consistency of Catalog Information
Querying on Catalog
Administrator's Guide
Controlling CUBRID Processes
Controlling CUBRID Service
Controlling Database Server
Controlling Broker
Controlling CUBRID Manager Server
Controlling CUBRID HA
Controlling CUBRID SHARD
CUBRID Services
Registering Services
Starting Services
Stopping Services
Restarting Services
Managing Service Status
Database Server
Starting Database Server
Stopping Database Server
Restarting Database Server
Checking Database Server Status
Limiting Database Server Access
Database Server Log
Error Log
Event Log
Database Server Errors
Broker
Starting Broker
Stopping Broker
Restarting Broker
Checking Broker Status
Limiting Broker Server Access
Managing a Specific Broker
Dynamically Changing Broker Parameters
Broker configuration information
Broker Logs
Checking the Access Log
Checking the Error Log
Managing the SQL Log
CAS Error
CUBRID Manager Server
Starting the CUBRID Manager Server
Stopping the CUBRID Manager Server
CUBRID Manager Server Log
Configuring CUBRID Manager Server
CUBRID Manager User Management Console
cubrid Management Utilities
Database Users
databases.txt File
Creating Database, Adding Volume, Deleting Database
Creating Database
Adding Database Volume
Deleting Database
Renaming Database, Altering Host, Copying/Moving Database, Registering Database
Renaming Database
Renaming Database Host
Copying/Moving Database
Registering Database
Database Backup
Backup Strategy and Method
Managing Backup Files
Managing Archive Logs
Restoring Database
Restoring Strategy and Procedure
Restoring Database to Different Server
Unloading and Loading Database
Unloading Database
Loading Database
How to Write Files to Load Database
Comment
Command Line
Data Line
Migrating Database
Checking and Compacting Database Space
Checking Used Space
Compacting Used Space
Updating Statistics and Checking Query Plan
Updating Statistics
Checking the Query Plan Cache
Dumping Statistics Information of Server
Checking Lock, Checking Transaction, Killing Transaction
Checking Lock Status
Output Contents
Checking Transaction
Killing Transactions
Diagnosing database and dumping parameter
Checking Database Consistency
Dumping Internal Database Information
Dumping Parameters Used in Server/Client
Changing HA Mode, Copying/Applying Logs
System Parameters
Configuring the Database Server
Scope of Database Server Configuration
Changing Database Server Configuration
cubrid.conf Configuration File and Default Parameters
Connection-Related Parameters
Memory-Related Parameters
Disk-Related Parameters
Error Message-Related Parameters
Concurrency/Lock-Related Parameters
Logging-Related Parameters
Transaction Processing-Related Parameters
Statement/Type-Related Parameters
Query Plan Cache-Related Parameters
Utility-Related Parameters
HA-Related Parameters
Other Parameters
Broker Configuration
cubrid_broker.conf Configuration File and Default Parameters
Common Parameters
Parameter by Broker
Troubleshooting
Checking SQL Log
SQL log of CAS
Function getting CAS information
Application log
Slow query
Server Error Log
Detecting Inconsistency between Index and Data
Detecting Overflow Keys or Overflow Pages
Detecting log recovery time
Detecting a Deadlock
Detecting the change of HA status
Detecting HA split-brain
Detecting Fail-over, Fail-back
CUBRID HA
CUBRID HA Concept
Nodes and Groups
Processes
Servers
heartbeat Message
failover and failback
Broker Mode
CUBRID HA Features
Duplexing Servers
Duplexing Brokers
Log Multiplexing
Quick Start
Preparation
Creating Databases and Configuring Servers
Starting and Verifying CUBRID HA
Configuring and Starting Broker, and Verifying the Broker Status
Environment Configuration
cubrid.conf
HA or Not
Logging
Access
cubrid_ha.conf
Node
Access
Replication
cubrid_broker.conf
Access Target
databases.txt
JDBC Configuration
CCI Configuration
PHP Configuration
Running and Monitoring
cubrid heartbeat Utility
start
stop
reload
deregister
status
Registering HA to cubrid service
cubrid applyinfo
cubrid changemode
Monitoring CUBRID Manager HA
Structures of HA
Default Structure of HA
Multiple-Slave Node Structure
Load Balancing Structure
Multiple-Standby Server Structure
HA Constraints
Operational Scenarios
Scenario of Building New Slave Node
Operation Scenario during Read/Write Service
Operation Scenario during Read Service
Operation Scenario after Service Stop
Detection of Replication Mismatch and Rebuild
Detection of Replication Mismatch
HA Error Messages
Rebuilding Replication
CUBRID SHARD
Database Sharding
CUBRID SHARD Terminologies
CUBRID SHARD Main Features
Middleware Structure
Selecting a Shard DB through the Shard SQL Hint
Transaction Support
Quick Start
Configuration Example
Changing the shard Configurations
Starting Service and Monitoring
Writing a Sample
Configuration and Setup
Configuration
Default Configuration File, shard.conf
Setting Shard Metadata
Setting User-Defined Hash Function
Running and Monitoring
Starting CUBRID SHARD
Stopping CUBRID SHARD
Registering SHARD to cubrid service
Dynamic change of CUBRID SHARD parameters
Checking CUBRID SHARD configuration information
Checking CUBRID SHARD ID
Checking CUBRID SHARD status
Limit shard proxy access
Managing specific shard
CUBRID SHARD Log
SHARD PROXY Log
SHARD CAS Log
Constraints
API Reference
JDBC Driver
JDBC Overview
Installing and Configuring JDBC
JDBC Programming
Configuration Connection
Checking SQL LOG
Checking Foreign Key Information
Using Object Identifiers (OIDs) and Collections
Getting Auto Increment Column Value
Using BLOB/CLOB
JDBC Error Codes and Error Messages
JDBC Sample Program
JDBC API
CCI Driver
CCI Overview
CCI Programming
Writing CCI Applications
Configuring Library
Using BLOB/CLOB
CCI Error Codes and Error Messages
CCI Sample Program
CCI API Reference
PHP Driver
Installing and Configuring PHP
For Linux
For Windows
Building CUBRID PHP Driver from Source Code
For Linux
For Windows
PHP Programming
Connecting to a Database
Transactions and Auto-Commit
Processing Queries
PHP API
PDO Driver
Installing and Configuring PDO
Linux
Windows
PDO Programming
Data Source Name (DSN)
Predefined Constants
PDO Sample Program
Verifying CUBRID PDO Driver Version
Connecting to CUBRID
Executing a SELECT Statement
Executing an UPDATE Statement
Using prepare and bind
Using the PDO::getAttribute() Function
CUBRID PDO Extensions
PDO API
ODBC Driver
Configuring and Environment ODBC
ODBC Programming
Configuring Connection String
ASP Sample Program
ODBC API
OLE DB Driver
Installing and Configuring OLE DB
OLE DB Programming
Using Data Link Properties Dialog Box
Configuring Connection String
Multi-Threaded Programming in .NET Environment
OLE DB API
ADO.NET Driver
ADO.NET Overview
Installing and Configuring ADO.NET
ADO.NET Programming
A Simple Query/Retrieve Code
batch Commands
Connection String
CUBRID Collections
CUBRID BLOB/CLOB
CUBRID Metadata Support
DataTable Support
Transactions
Working with Parameters
Error Codes and Messages
NHibernate
Java Stored Procedure
ADO.NET API
Perl Driver
Installing and Configuring Perl
Perl API
Python Driver
Installing and Configuring Python
Linux/UNIX
Windows
Python Programming
Python Sample Program
Python API
Ruby Driver
Installing and Configuring Ruby
Ruby Sample Program
Ruby API
Node.js Driver
Installing Node.js
CUBRID Node.js Programming
Connection
Examples
CUBRID Node.js API
Release Notes
2008 R4.4 Release Notes
Release Notes Information
Overview
Improvements in Administrative Convenience
Stablization in HA replication
Improvements in Sharding Features
Behavioral Changes
Configuration
Driver Compatibility
New Features
Administrative Convenience
HA
Sharding
Configuration
Behavioral Changes
SQL
Driver
Utility
Configuration
Other
Improvements and Fixes
Performance and Optimization
Resource
Stability
SQL
HA
Sharding
Driver
Administrative Convenience
Utility
Configuration, Build, and Installation
Others
Cautions
New Cautions
Existing Cautions
General Information
Revision history
Bug Reports and User Feedback
License
Additional Information
Note on Drivers
CUBRID
Docs
»
CUBRID SQL Guide
»
Data Manipulation
View page source
Data Manipulation
¶
SELECT
FROM Clause
WHERE Clause
GROUP BY ... HAVING Clause
ORDER BY Clause
LIMIT Clause
Join Query
Subquery
Hierarchical Query
START WITH Clause
CONNECT BY Clause
Hierarchical Query Execution
Ordering Data with the Hierarchical Query
Pseudo Columns for Hierarchical Query
Operators for Hierarchical Query
Functions for Hierarchical Query
Examples of Hierarchical Query
Performance of Hierarchical Query
INSERT
INSERT ... SELECT Statement
ON DUPLICATE KEY UPDATE Clause
UPDATE
REPLACE
DELETE
TRUNCATE
PREPARED STATEMENT
PREPARE Statement
EXECUTE Statement
DEALLOCATE PREPARE/DROP PREPARE Statements
SET
DO
SHOW
DESC, DESCRIBE
EXPLAIN
SHOW TABLES
SHOW COLUMNS
SHOW INDEX
SHOW GRANTS
SHOW CREATE VIEW
SHOW EXEC STATISTICS